/** 
 * @Title: BrandFor3857oDao.java 
 * @Package com.zizo.autohome.word.3857o.dao 
 * @Description: TODO
 * @author HeYan
 * @date 2016年9月7日 下午2:54:49 
 */
package com.zizo.autohome.word.flowtrend.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import com.zizo.autohome.emotion.alg.AhoCorasickDoubleArrayTrie;
import com.zizo.autohome.sta.slrank.util.SlrankUtil;
import com.zizo.util.ConnectionPool;
import com.zizo.util.Util;

/**
 * @ClassName: BrandFor3857oDao
 * @Description: TODO
 * @author HeYan
 * @date 2016年9月7日 下午2:54:49
 * 
 */
public class AllOutDao implements Runnable {
	private Connection conn168;
	private Connection conn198;
	private int cid;
	private int clubid;
	private AhoCorasickDoubleArrayTrie<Integer> acdatLocal;
	private Map<Integer, List<String>> wordMap;
	private Map<Integer, int[]> carMap;// CarMap key=id value=BizID,CarID,ClubID
	private Map<Integer, String> carName;
	private Map<Integer, List<Integer>> idMap;
	private Map<String, String> level;// 城市线级和区域
	private String tablename;
	private int bizID;
	private String bizName;

	public AllOutDao(Connection conn168, Connection conn198, int cid, int clubid,
			AhoCorasickDoubleArrayTrie<Integer> acdatLocal, Map<Integer, List<String>> wordMap,
			Map<Integer, int[]> carMap, Map<Integer, String> carName, Map<Integer, List<Integer>> idMap,
			Map<String, String> level, String tablename) {
		super();
		this.conn168 = conn168;
		this.conn198 = conn198;
		this.cid = cid;
		this.clubid = clubid;
		this.acdatLocal = acdatLocal;
		this.wordMap = wordMap;
		this.carMap = carMap;
		this.carName = carName;
		this.idMap = idMap;
		this.level = level;
		this.tablename = tablename;
		String res = getBizIDAndCarName();
		bizID = Integer.parseInt(res.split("%%")[0]);
		bizName = (res.split("%%")[1]);
	}

	public final void getDataFromBBS() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT bbs_title,bbs_content,bbs_ptime,bbs_sf,bbs_cs,bbs_bbsid FROM autohome_syn.bbs_c_" + clubid;
		// + " where bbs_ptime>'2016-01-01'";
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String bbs_bt = rs.getString("bbs_title");
				String bbs_nr = rs.getString("bbs_content");
				String bbs_ftsj = rs.getString("bbs_ptime");
				String bbs_sf = rs.getString("bbs_sf");
				String bbs_ds = rs.getString("bbs_cs");
				bbs_ds = bbs_ds.length() > 1 ? bbs_ds : bbs_sf;
				int bbs_id = rs.getInt("bbs_bbsid");
				String rank = "";
				String area = "";
				try {
					if (bbs_ds.length() > 1) {
						rank = level.get(bbs_ds).split("-")[0];
						area = level.get(bbs_ds).split("-")[1];
					} else {
						if (!bbs_sf.equals("其他")) {
							rank = level.get(bbs_sf).split("-")[0];
							area = level.get(bbs_sf).split("-")[1];
						}
					}
				} catch (Exception e) {
				}
				for (String str : bbs_bt.split("\n|\\}\\{|\\?")) {
					List<Integer> ids = acdatLocal.parseTextNotHit(str);
					if (ids.size() == 0) {
						ids = null;
						continue;
					}
					Set<Integer> set = new HashSet<>();
					for (int s : ids) {
						Set<Integer> ll = new HashSet<>(idMap.get(s));
						set.addAll(ll);
					}
					ids = null;
					List<Integer> idList = new ArrayList<>(set);
					set = null;
					List<Integer> idTemp = new ArrayList<>(idList);
					for (int i : idTemp) {
						List<String> word = wordMap.get(i);
						boolean flag = true;
						for (String w : word) {
							flag = str.contains(w);
							if (!flag) {
								break;
							}
						}
						if (!flag) {
							idList.remove((Integer) i);
						}
						word = null;
					}
					idTemp = null;
					List<Integer> hitList = checkSub(idList);
					idList = null;
					for (int i : hitList) {
						int[] carids = carMap.get(i);
						StringBuffer sb = new StringBuffer();
						for (String ww : wordMap.get(i)) {
							sb.append(ww + "  ");
						}
						insertLania(bizID, bizName, carids[0], carName.get(i), sb.toString().trim(), bbs_ftsj, str,
								bbs_sf, bbs_ds, bbs_id, rank, area);
					}
					hitList = null;
				}
				for (String str : bbs_nr.split("\n|\\}\\{|\\?")) {
					List<Integer> ids = acdatLocal.parseTextNotHit(str);
					if (ids.size() == 0) {
						ids = null;
						continue;
					}
					Set<Integer> set = new HashSet<>();
					for (int s : ids) {
						Set<Integer> ll = new HashSet<>(idMap.get(s));
						set.addAll(ll);
					}
					List<Integer> idList = new ArrayList<>(set);
					set = null;
					List<Integer> idTemp = new ArrayList<>(idList);
					for (int i : idTemp) {
						List<String> word = wordMap.get(i);
						boolean flag = true;
						for (String w : word) {
							flag = str.contains(w);
							if (!flag) {
								break;
							}
						}
						if (!flag) {
							idList.remove((Integer) i);
						}
					}
					idTemp = null;
					List<Integer> hitList = checkSub(idList);
					idList = null;
					for (int i : hitList) {
						int[] carids = carMap.get(i);
						StringBuffer sb = new StringBuffer();
						for (String ww : wordMap.get(i)) {
							sb.append(ww + "  ");
						}
						insertLania(bizID, bizName, carids[0], carName.get(i), sb.toString().trim(), bbs_ftsj, str,
								bbs_sf, bbs_ds, bbs_id, rank, area);
					}
					hitList = null;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public final void getDataFromKB() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT kb_content,kb_ptime,kb_province,kb_area,kb_kbid FROM autohome_syn.autohome_kb"
				+ " WHERE kb_cid=" + cid;// + " and kb_ptime>'2016-01-01'";
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String bbs_nr = rs.getString("kb_content");
				String bbs_ftsj = rs.getString("kb_ptime");
				String bbs_sf = rs.getString("kb_province");
				bbs_sf = bbs_sf == null ? "" : bbs_sf;
				String bbs_ds = rs.getString("kb_area");
				bbs_ds = bbs_ds == null ? "" : bbs_ds;
				int kb_id = rs.getInt("kb_kbid");
				String rank = "";
				String area = "";
				if (bbs_sf.length() > 1) {
					if (!bbs_sf.equals("其他")) {
						String s1 = level.get(bbs_sf);
						if (s1 != null) {
							rank = level.get(bbs_sf).split("-")[0];
							area = level.get(bbs_sf).split("-")[1];
						}
					}
				}
				for (String str : bbs_nr.split("\n|\\}\\{|\\?")) {
					List<Integer> ids = acdatLocal.parseTextNotHit(str);
					if (ids.size() == 0) {
						ids = null;
						continue;
					}
					Set<Integer> set = new HashSet<>();
					for (int s : ids) {
						Set<Integer> ll = new HashSet<>(idMap.get(s));
						set.addAll(ll);
					}
					ids = null;
					List<Integer> idList = new ArrayList<>(set);
					set = null;
					List<Integer> idTemp = new ArrayList<>(idList);
					for (int i : idTemp) {
						List<String> word = wordMap.get(i);
						boolean flag = true;
						for (String w : word) {
							flag = str.contains(w);
							if (!flag) {
								break;
							}
						}
						if (!flag) {
							idList.remove((Integer) i);
						}
						word = null;
					}
					idTemp = null;
					List<Integer> hitList = checkSub(idList);
					idList = null;
					for (int i : hitList) {
						int[] carids = carMap.get(i);
						StringBuffer sb = new StringBuffer();
						for (String ww : wordMap.get(i)) {
							sb.append(ww + "  ");
						}
						insertLaniaKB(bizID, bizName, carids[0], carName.get(i), sb.toString().trim(), bbs_ftsj, str,
								bbs_ds, bbs_sf, kb_id, rank, area);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @Title: checkSub
	 * @Description: 去除子集操做
	 * @param list
	 * @return
	 * @return List<Integer>
	 * @author Alexis
	 */
	public final List<Integer> checkSub(List<Integer> list) {
		List<Integer> res = new ArrayList<>();
		res.addAll(list);
		for (int i = 0; i < list.size(); i++) {
			for (int j = i + 1; j < list.size(); j++) {
				List<String> s1 = wordMap.get(list.get(i));
				List<String> s2 = wordMap.get(list.get(j));
				int l1 = 0;
				int l2 = 0;
				StringBuffer sb1 = new StringBuffer();
				StringBuffer sb2 = new StringBuffer();
				for (String s : s1) {
					l1 += s.length();
					sb1.append(s);
				}
				for (String s : s2) {
					l2 += s.length();
					sb2.append(s);
				}
				if (l1 >= l2) {
					boolean flag = true;
					String c = sb1.toString();
					for (String s : s2) {
						flag = c.contains(s);
						if (!flag) {
							break;
						}
					}
					if (flag) {
						res.remove((Integer) list.get(j));
					}
				} else {
					boolean flag = true;
					String c = sb2.toString();
					for (String s : s1) {
						flag = c.contains(s);
						if (!flag) {
							break;
						}
					}
					if (flag) {
						res.remove((Integer) list.get(i));
					}
				}
				s1 = null;
				s2 = null;
			}
		}
		return res;
	}

	public final int getBizID(int carid) {
		Statement st = null;
		ResultSet rs = null;
		String sql = "select brand_id from carvoice_biz.biz_brand where brand_autohome=" + carid;
		int res = 0;
		try {
			st = conn168.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return res;
	}

	public final List<List<String>> getCarAndAlias() {
		List<List<String>> list = new ArrayList<>();
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT CarID,CarName,Alias_1,Alias_2,Alias_3,Alias_4,BizID,ClubID,id FROM Projects_2016.brand_models_alias_new";
		try {
			st = conn198.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int carid = rs.getInt("CarID");
				String carname = rs.getString("CarName");
				int bizid = rs.getInt("BizID");
				int clubid = rs.getInt("ClubID");
				int id = rs.getInt("id");
				String w1 = rs.getString(3);
				String w2 = rs.getString(4);
				String w3 = rs.getString(5);
				String w4 = rs.getString(6);
				if (w1 == null || w1.equals("")) {
					w1 = "";
				}
				if (w2 == null || w2.equals("")) {
					w2 = "";
				}
				if (w3 == null || w3.equals("")) {
					w3 = "";
				}
				if (w4 == null || w4.equals("")) {
					w4 = "";
				}
				List<String> l = new ArrayList<>();
				l.add(id + "");
				l.add(bizid + "");
				l.add(carid + "");
				l.add(clubid + "");
				l.add(carname);
				l.add(w1);
				l.add(w2);
				l.add(w3);
				l.add(w4);
				list.add(l);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public final int getLenth(List<String> list) {
		int length = 0;
		StringBuffer sb = new StringBuffer();
		for (int i = 2; i < 6; i++) {
			sb.append(list.get(i));
		}
		length = sb.length();
		return length;
	}

	public final void createTable() {
		Statement st = null;
		String s1 = "DROP table if exists all_in_out_bbs_" + tablename;
		String s2 = "DROP table if exists all_in_out_kb_" + tablename;
		String sql = "CREATE TABLE all_in_out_bbs_" + tablename + " LIKE all_in_out_bbs;";
		String sql1 = "CREATE TABLE all_in_out_kb_" + tablename + " LIKE all_in_out_kb;";
		try {
			st = conn198.createStatement();
			st.executeUpdate(s1);
			st.executeUpdate(s2);
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public final void insertLaniaKB(int incid, String incname, int outcid, String outcname, String word, String time,
			String content, String pro, String area, int bbsid, String level, String region) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO Projects_2016.all_in_out_kb_" + tablename
				+ "(flowtrend_cid,flowtrend_time,flowtrend_content,flowtrend_province,flowtrend_area,flowtrend_carid,flowtrend_cidname,flowtrend_kbid,flowtrend_carname,flowtrend_level,flowtrend_region,flowtrend_word) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn198.prepareStatement(sql);
			ps.setInt(1, incid);
			ps.setTimestamp(2, (Timestamp) Util.getTimeHMS(time));
			ps.setString(3, content);
			ps.setString(4, pro);
			ps.setString(5, area);
			ps.setInt(6, outcid);
			ps.setString(7, incname);
			ps.setInt(8, bbsid);
			ps.setString(9, outcname);
			ps.setString(10, level);
			ps.setString(11, region);
			ps.setString(12, word);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// BBS
	public final void insertLania(int incid, String incname, int outcid, String outcname, String word, String time,
			String content, String pro, String area, int bbsid, String level, String region) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO Projects_2016.all_in_out_bbs_" + tablename
				+ "(flowtrend_cid,flowtrend_time,flowtrend_content,flowtrend_province,flowtrend_area,flowtrend_carid,flowtrend_cidname,flowtrend_bbsid,flowtrend_carname,flowtrend_level,flowtrend_region,flowtrend_word) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn198.prepareStatement(sql);
			ps.setInt(1, incid);
			ps.setTimestamp(2, (Timestamp) Util.getTimeHMS(time));
			ps.setString(3, content);
			ps.setString(4, pro);
			ps.setString(5, area);
			ps.setInt(6, outcid);
			ps.setString(7, incname);
			ps.setInt(8, bbsid);
			ps.setString(9, outcname);
			ps.setString(10, level);
			ps.setString(11, region);
			ps.setString(12, word);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void run() {
		getDataFromBBS();
		getDataFromKB();
		System.out.println(cid);
	}

	/**
	 * @Title: getAutohomeID
	 * @Description: 得到汽车之家的口碑ID&&BBSID
	 * @return
	 * @return List<Integer>
	 * @author Alexis
	 */
	public final Map<Integer, Integer> getAutohomeID() {
		Statement st = null;
		ResultSet rs = null;
		Map<Integer, Integer> map = new HashMap<>();
		String sql = "SELECT distinct CarID,ClubID FROM Projects_2016.brand_models_alias_new where CarID!=0 AND ClubID IS NOT NULL";
		try {
			st = conn198.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				map.put(rs.getInt(2), rs.getInt(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}

	public final String getBizIDAndCarName() {
		Statement st = null;
		ResultSet rs = null;
		String res = "";
		String sql = "select BizID,CarName from brand_models_alias_new where CarID=" + cid + " limit 1";
		try {
			st = conn198.createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				res = rs.getInt(1) + "%%" + rs.getString(2);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return res;
	}

	public AllOutDao(Connection conn168, Connection conn198, String tablename) {
		super();
		this.conn168 = conn168;
		this.conn198 = conn198;
		this.tablename = tablename;
	}

	public static void main(String[] args) throws SQLException {
		long start = System.currentTimeMillis();
		ExecutorService fix = Executors.newFixedThreadPool(10);// 50个线程
		Connection conn198 = ConnectionPool.getConnection("Projects_2016", "localhost", "3306");
		Connection conn168 = ConnectionPool.getConnection("autohome_syn", "192.169.0.168", "3306");
		String tablename = "0112";
		AllOutDao dao = new AllOutDao(conn168, conn198, tablename);
		dao.createTable();
		List<List<String>> list = dao.getCarAndAlias();
		Map<String, String> level = SlrankUtil.getCityLevel();// 城市的线级
		Map<String, Integer> aliasMap = new HashMap<>();// 车型别名的Map表，key=alias
														// ,Value=id
		Map<Integer, List<String>> wordMap = new HashMap<>();// 车型别名的Map表，key=id
		// ,Value=alias
		Map<Integer, int[]> carMap = new HashMap<>();// CarMap key=id
														// value=BizID,CarID,ClubID
		Map<Integer, String> carName = new HashMap<>();// CarName key=id
														// ,value=CarName
		Map<Integer, List<Integer>> idMap = new HashMap<>();
		for (List<String> alias : list) {
			int id = Integer.parseInt(alias.get(0));
			int bizid = Integer.parseInt(alias.get(1));
			int carid = Integer.parseInt(alias.get(2));
			int clubid = Integer.parseInt(alias.get(3));
			String carname = alias.get(4);
			String w1 = alias.get(5);
			String w2 = alias.get(6);
			String w3 = alias.get(7);
			String w4 = alias.get(8);
			List<String> st = new ArrayList<>();
			if (w1.length() > 0) {
				st.add(w1);
				if (aliasMap.containsKey(w1)) {
					int idMapKey = aliasMap.get(w1);
					List<Integer> l = idMap.get(idMapKey);
					l.add(id);
					idMap.put(idMapKey, l);
				} else {
					aliasMap.put(w1, id);
					List<Integer> l = new ArrayList<>();
					l.add(id);
					idMap.put(id, l);
				}
			}
			if (w2.length() > 0) {
				st.add(w2);
				if (aliasMap.containsKey(w2)) {
					int idMapKey = aliasMap.get(w2);
					List<Integer> l = idMap.get(idMapKey);
					l.add(id);
					idMap.put(idMapKey, l);
				} else {
					aliasMap.put(w2, id);
					List<Integer> l = new ArrayList<>();
					l.add(id);
					idMap.put(id, l);
				}
			}
			if (w3.length() > 0) {
				st.add(w3);
				if (aliasMap.containsKey(w3)) {
					int idMapKey = aliasMap.get(w3);
					List<Integer> l = idMap.get(idMapKey);
					l.add(id);
					idMap.put(idMapKey, l);
				} else {
					aliasMap.put(w3, id);
					List<Integer> l = new ArrayList<>();
					l.add(id);
					idMap.put(id, l);
				}
			}
			if (w4.length() > 0) {
				st.add(w4);
				if (aliasMap.containsKey(w4)) {
					int idMapKey = aliasMap.get(w4);
					List<Integer> l = idMap.get(idMapKey);
					l.add(id);
					idMap.put(idMapKey, l);
				} else {
					aliasMap.put(w4, id);
					List<Integer> l = new ArrayList<>();
					l.add(id);
					idMap.put(id, l);
				}
			}
			int[] ids = new int[3];
			ids[0] = bizid;
			ids[1] = carid;
			ids[2] = clubid;
			carMap.put(id, ids);
			carName.put(id, carname);
			if (!wordMap.containsKey(id)) {
				wordMap.put(id, st);
			}
		}
		/* 双Trie树 */
		AhoCorasickDoubleArrayTrie<Integer> acdatLocal = new AhoCorasickDoubleArrayTrie<Integer>();
		acdatLocal.build(aliasMap);
		for (Map.Entry<Integer, Integer> entry : dao.getAutohomeID().entrySet()) {
			fix.execute(new AllOutDao(conn168, conn198, entry.getValue(), entry.getKey(), acdatLocal, wordMap, carMap,
					carName, idMap, level, tablename));
		}
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				String sql = "UPDATE Projects_2016.all_in_out_kb_" + tablename
						+ " AS a INNER JOIN carvoice_core.core_area AS b ON a.flowtrend_area=b.area_city SET a.flowtrend_province=b.area_province;";
				conn198.createStatement().executeUpdate(sql);
				System.out.println((System.currentTimeMillis() - start) / (1000 * 60) + "Minute");
				break;
			} else {
				try {
					Thread.sleep(1000 * 600);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
